Consultoría de Bancos

Bases de Datos………………………………………………………………………………………………………. 2Enunciado………………………………………………………………………………………………………….2**Diseño conceptual…………………………………………………………………………………………….. 4**Diseño lógico……………………………………………………………………………………………………..4**Entidades……………………………………………………………………………………………………… 4Relaciones N:M o N:M:P………………………………………………………………………………….5Normalización…………………………………………………………………………………………………5Diseño en MySQL Workbench…………………………………………………………………………….6Consultas……………………………………………………………………………………………………… 9Microsoft Access………………………………………………………………………………………………14Importación de los datos……………………………………………………………………………….. 14Subformularios…………………………………………………………………………………………….. 17MongoDB………………………………………………………………………………………………………… 20Paso de tablas a NoSQL………………………………………………………………………………..20Banco……………………………………………………………………………………………………. 21Pais………………………………………………………………………………………………………..21Cuenta……………………………………………………………………………………………………21Cliente…………………………………………………………………………………………………… 22Empleado………………………………………………………………………………………………. 23Consultas……………………………………………………………………………………………………. 23Página web…………………………………………………………………………………………………………… 34HTML………………………………………………………………………………………………………………..35CSS………………………………………………………………………………………………………………….36JavaScript mouseover…………………………………………………………………………………………39Párrafo…………………………………………………………………………………………………………39Imágenes……………………………………………………………………………………………………..39

JS sugerencias…………………………………………………………………………………………………..41Posible mejora………………………………………………………………………………………………43Consultas…………………………………………………………………………………………………………. 43Consulta_nombres……………………………………………………………………………………….. 43Consulta_transacciones…………………………………………………………………………………44Insertar, eliminar datos…………………………………………………………………………………..44Problema surgido de array de arrays………………………………………………………….44Consulta SQL complicada………………………………………………………………………………46

Bases de Datos

Enunciado

BankingConsulting quiere una base de datos en la que almacena la información relativa a bancos.

De un país se desea guardar su Nombre y Población y un ID.

De los bancos su sede, nombre e ID.

De las divisas la tasa de cambio e ID.

De las cuentas sus balances, tipo (depósito o cuenta corriente) e iD.

De las transacciones la fecha, cantidad, descripción e ID.

De los préstamos la cantidad, fecha inicio, fecha vencimiento e ID.

De los empleados su nombre, apellido e ID.

Diseño conceptual

Diseño lógico

Entidades

Subrayado indica que dicho atributo(s) es(son) “Primary Key”

  1. País (ID_País, Nombre, Población,ID_Divisa)
    1. FK: ID_Divisa -> Divisa
  2. Banco (ID_Banco, Nombre, Sede)
  3. Préstamo (ID_Préstamo, Monto, Fecha_inicio, Fecha_vencimiento)
  4. Cliente (ID_Cliente, Nombre, Apellido, Fecha_nacimiento)
  5. Cuenta (ID_Cuenta, Tipo, Balance, ID_Banco)
    1. FK: ID_Banco -> Banco
  6. Transacción (ID_Transacción, Fecha, Monto, Descripción,ID_Divisa, ID_Cuenta)
  7. FK: ID_Divisa -> Divisa
  8. FK: ID_Cuenta
  9. Divisa (ID_Divisa, Código, Tasa_cambio)
  10. Empleado (ID_Empleado, Nombre, Apellido, ID_Banco)
  11. Supervisor(ID_Supervisor, ID_Empleado)
  1. FK: ID_Empleado -> Empleado

10.Miembro_Equipo(ID_Miembro_Equipo, ID_Empleado, ID_Supervisor)

  1. FK: ID_Empleado -> Empleado
  2. FK: ID_Supervisor -> Supervisor

En Divisa el código podría haber sido “Primary Key” y en Supervisor y Miembro_Equipo el ID_Empleado podría haber funcionado como “Primary Key”, sin embargo, prefiero añadir nuevos IDs por guardar la consistencia con el resto de entidades.

Relaciones N:M o N:M:P

  1. opera(ID_País, ID_Banco)
  2. solicita (ID_Banco,ID_Prestamo,ID_Cliente)
  3. tiene(ID_Cliente,ID_Cuenta)

Normalización

Vamos a estudiar si el esquema lógico está en 3FN. Primero, usando la definición de clase de 1FN:

“Una tabla relacional R está en primera forma normal (1FN) si NO contiene campos multivaluados (cada atributo 1 valor).”

Como nuestro diseño no tiene campo multievaludados, concluimos que está en 1FN.

Para ver si está en 2FN es necesario que esté en 1FN y, además, que no exista dependencia transitiva entre campos no principales y claves candidatas.

Luego todas las tablas están en 2FN

Y no hay dependencias transitivas, luego, como es 2FN, también es 3FN.

Diseño en MySQL Workbench

Primero he generado los archivos SQL:

He usado la herramienta de “reverse engineering” para hacer el diseño lógico en MySQL Workbench.

  1. Hacemos click en “Create EER Model from Script”:

  1. Añadimos el Script SQL donde se crean las tablas, seleccionamos “Place imported objects on a diagram” y le damos a “Execute”, luego “Next” y luego “Finish”.

Obtenemos el diagrama lógico

Archivo del modelo de MySQL workbench:

Consultas

Las consultas están en el archivo queries.sql

  1. clientes que hayan nacido después de 1960

SELECT * FROM Cliente WHERE Fecha_Nacimiento > ‘1960-12-31’;ref1

  1. clientes cuyos nombres empiecen por D

SELECT * FROM Cliente WHERE Nombre LIKE ‘D%’;ref2

  1. clientes que tengan más de 2 cuentas

SELECT Nombre

FROM Cliente WHERE ID_Cliente IN

(SELECT ID_Cliente FROM Cliente_Cuenta GROUP BY ID_Cliente

HAVING COUNT(ID_Cuenta) > 1);

  1. ids de los clientes que tengan cuenta en el Banco Bilbao y Deutsche Bank

SELECT ID_Cliente

FROM Cliente_Banco WHERE ID_Banco IN (SELECT ID_Banco FROM Banco WHERE Nombre IN (‘Banco Bilbao’, ‘Deutsche Bank’));

  1. id de los clientes cuya posicion neta (suma de balances de sus cuentas) sea negativa

SELECT ID_Cliente, Nombre

FROM Cliente WHERE ID_Cliente IN

(SELECT ID_Cliente FROM Cuenta

INNER JOIN Cliente_Cuenta ON Cuenta.ID_Cuenta = Cliente_Cuenta.ID_Cuenta GROUP BY ID_Cliente HAVING SUM(Balance) < 0);

  1. ids de las transacciones que se hagan a una cuenta con divisa distinta a la propia de la transacción

SELECT ID_Transaccion FROM Transaccionref3

INNER JOIN Cuenta ON Transaccion.ID_Cuenta = Cuenta.ID_Cuenta WHERE Transaccion.ID_Divisa != Cuenta.ID_Divisa;

  1. cantidad a ingresar en la divisa de la cuenta de las transacciones que tienen una divisa distinta a la de la cuenta

SELECT Transaccion.Monto * D1.Tasa_cambio*1/D2.Tasa_cambio

FROM Transaccion

– Join de la tabla de transacciones con la tabla de cuentas

INNER JOIN Cuenta ON Transaccion.ID_Cuenta = Cuenta.ID_Cuenta

– Hago el JOIN para obtener Tasa de cambio de la moneda de la transacción INNER JOIN Divisa as D1 ON Transaccion.ID_Divisa = D1.ID_Divisa

– Hago el JOIN para obtener Tasa de cambio de la moneda de la cuenta INNER JOIN Divisa as D2 ON Cuenta.ID_Divisa = D2.ID_Divisa

WHERE Transaccion.ID_Divisa != Cuenta.ID_Divisa;

  1. Mostar el nombre de los clientes que tenga alguna cuenta con divisa distinta a euros

SELECT Nombre FROM Cliente WHERE ID_Cliente IN

(SELECT ID_Cliente FROM Cliente_Cuenta WHERE ID_Cuenta IN (SELECT ID_Cuenta FROM Cuenta WHERE ID_Divisa != 1));

  1. Mostrar los paises que no tengan mas de 2 bancos SELECT ID_Pais FROM Banco_Pais

GROUP BY ID_Pais HAVING COUNT(ID_Banco) <= 2;

  1. Mostrar las divisas que no tengan ninguna cuenta asociada

SELECT * FROM Divisa

WHERE ID_Divisa NOT IN (SELECT ID_Divisa FROM Cuenta);

  1. Mostrar las divisas que se usen en menos de dos paises

SELECT ID_Divisa FROM Pais

GROUP BY ID_Divisa HAVING COUNT(ID_Pais) < 2;

AÑADIR FOTO

  1. Mostrar los prestamos que tengan una durancion mayor a 10 años

SELECT * FROM Prestamo WHERE DATEDIFF(Fecha_fin, Fecha_inicio) > 365*10;

  1. Mostrar los clientes que tengan un prestamo con una duracion mayor a 10 años

SELECT ID_Cliente FROM Prestamo

WHERE DATEDIFF(Fecha_fin, Fecha_inicio) > 365*10;

  1. Mostrar los clientes que tengan un prestamo en un banco en el que no tienen ninguna cuenta

SELECT Cliente_Prestamo_Banco.ID_Cliente FROM Cliente_Prestamo_Banco INNER JOIN Cliente_Banco

ON Cliente_Prestamo_Banco.ID_Cliente = Cliente_Banco.ID_Cliente

WHERE Cliente_Prestamo_Banco.ID_Banco != Cliente_Banco.ID_Banco;

  1. Mostrar las divisas con las que hay una cantidad transferida total equivalente a más de 500 euros

SELECT Transaccion.Monto*Divisa.Tasa_cambio, Transaccion.* FROM Transaccionref3

INNER JOIN Divisa ON Transaccion.ID_Divisa = Divisa.ID_Divisa;

  1. Comprobar si las cuentas asociadas que tenga el cliente está él sólo y si es así borrar la cuenta también.

Usado en Problema surgido de array de arrays

SELECT ID_Cuenta FROM Cliente_Cuenta WHERE ID_Cuenta IN

(SELECT ID_Cuenta

FROM Cliente_Cuenta

GROUP BY ID_Cuenta

HAVING COUNT(*) = 1)

AND ID_Cliente = 1

Microsoft Access

Vídeos útiles:

Importación de los datos

Exportamos los datos de phpmyadmin seleccionando la tabla que queramos exportar y los datos de la tabla que deseemos y haciendo click sobre export. Además escogemos el formato “CSV for MS Excel”.

Luego en la foto pinchamos en Archivo de texto

En examinar escogemos el archivo csv que hemos exportamos y en la imagen siguiente vemos dónde escogemos la tabla donde queremos importar los datos pulsamos aceptar y acabamos.

Subformularios

Son útiles para mostrar datos de tablas con relaciones N:M. Por ejemplo, vamos a crear un formulario para mostrar los bancos que operan en un determinado país.

Primero pinchamos en Asistente para formularios.

Luego elegimos los campos que queremos que se muestren de cada tabla.

Por último, elegimos agrupar los bancos por país.

Pinchamos en ver para ir a vista de diseño. Pinchamos sobre botones. Elegimos un botón para navegar a anterior y siguiente registro.

Nos queda el formulario con subformulario tal que así:

MongoDB

Primero hay que adaptar la parte del modelo entidad relación a colecciones (NoSQL). En la siguiente imagen se puede ver en verde la parte del modelo que voy a adaptar.

Paso de tablas a NoSQL

Banco

Colección con Nombre, Sede, ID (los genera automáticamente Mongo DB) y para conservar la relación entre País y Banco creo una colección con un array con los IDs de Pais.

Pais

Nombre, Poblacion y colección llamada Divisa con Código y Tasa de Cambio.

Cuenta

Divisa como subcolección y relación con Cliente mediante array de códigos.

Cliente

Empleado

Para adaptar la relación jerárquica vamos a poner un campo Supervisor_ID, que será null para los supervisores (no son supervisados por nadie) pero cuando sea un miembro de un equipo (no supervisor) tendrá el ID del supervisor asignado.

Los archivos JSON anteriores se encuentran en github:

Consultas

Las consultas siguientes se encuentran en el siguiente fichero consultas.txt

  1. Obtener todos los bancos con sede en “Londres” EconomiaDB.Banco.find({ “Sede”: “Londres” })

  1. Encontrar todas las cuentas de ahorro con un saldo mayor de 1000 euros db.Cuenta.find({ “Tipo”: “Ahorros”, “Balance”: { $gt: 1000 } })

  1. Buscar empleados que trabajen en un banco con sede en “Londres” db.Empleado.find({ “ID_Banco”: { $in: [2, 7] } })

  1. Encontrar todos los clientes cuyo nombre sea “Juan” db.Cliente.find({ “Nombre”: “Juan” })ref4

  1. Obtener la población de todos los países con una población mayor de 50 millones de habitantes

db.Pais.find({ “Poblacion”: { $gt: 50 } })ref1

  1. Encontrar todas las cuentas en euros (EUR) con un saldo mayor de 1000 euros db.Cuenta.find({ “Divisa.Codigo”: “EUR”, “Balance”: { $gt: 1000 } })ref4

  1. Encontrar todos los empleados que no tienen supervisor, es decir, los supervisores. db.Empleado.find({ “Supervisor_ID”: null })ref4

  1. Encontrar los bancos que operan en más de 5 países db.Banco.aggregate([

{

$project: {

Nombre: 1,

Sede: 1,

NumPaises: { \(size: "\)Pais” }

}

}, {

$match: {

NumPaises: { $gt: 5 }

}

}

]) ó

db.Banco.find({

$where: “this.Pais.length > 5” })

  1. Obtener el nombre y apellido de los empleados que trabajan en el banco con ID 1 db.Empleado.find({ “ID_Banco”: 1 }, { “Nombre”: 1, “Apellido”: 1 })ref4

  1. Encontrar las cuentas de ahorro con un saldo negativo db.Cuenta.find({ “Tipo”: “Ahorros”, “Balance”: { $lt: 0 } })ref2

CORREGIR

  1. Encontrar las cuentas de ahorro con un saldo negativo

db.Cuenta.find({ “Tipo”: “Ahorros”, “Balance”: { $lt: 0 } })

  1. Encuentra los tres bancos con el mayor número de sucursales (países en los que operan)

db.Banco.aggregate([

{ $project: { “Nombre”: 1, “Numero_Paises”: { \(size: "\)Pais” } } }, { $sort: { “Numero_Paises”: -1 } },

{ $limit: 3 }

])

  1. Encuentra el cliente más joven db.Cliente.aggregate([

{

$project: {

“Nombre”: 1,

“Apellido”: 1,

“Fecha_nacimiento”: { \(toDate: "\)Fecha_nacimiento” },

“Edad”: {

$floor: {

$divide: [

{ $subtract: [new Date(), { \(toDate: "\)Fecha_nacimiento” }] }, 31536000000 // Milisegundos en un año

]

}

}

}

},

{ $sort: { “Edad”: 1 } },

{ $limit: 1 }

])

  1. Encuentra el saldo total de todas las cuentas en euros (EUR) para cada banco

db.Cuenta.aggregate([

{ $match: { “Divisa.Codigo”: “EUR” } }, {

$group: {

_id: “$ID_Banco”,

Saldo_Total: { \(sum: "\)Balance” }

}

}

])

  1. Encuentra la edad promedio de los empleados en el banco con ID 1

db.Empleado.aggregate([

{

$match: { “ID_Banco”: 1 }

},

{

$project: {

“Fecha_contratacion”: { \(toDate: "\)Fecha_contratacion” }, “Edad”: {

$floor: {

$divide: [

{ $subtract: [new Date(), { \(toDate: "\)Fecha_contratacion” }] }, 31536000000 // Milisegundos en un año

]

}

}

}

}, {

$group: {

_id: null,

EdadPromedio: { \(avg: "\)Edad” } }

}

])

  1. Encuentra los clientes que no tienen cuentas en ningún banco.

db.Cliente.find({

“ID_Cliente”: { $nin: db.Cuenta.distinct(“ID_Cliente”) } })

Página web

Bankconsulting nos ha pedido diseñar una página web en la que puedan describir la empresa y su filosofía. Además, quieren poder filtrar los nombres de los clientes por su nombre y/o apellidos y ver la consultas más nuevas de una fecha dada y una divisa seleccionada.

Se puede visitar la web en github: https://danmorper.github.io/ pero no tiene la parte de servidor.

Primero creamos el archivo index.html. Luce tal que así:

He usado bootstrap5 para estructurar la página.

Se compone de una barra de navegación fija en la parte de arriba de la pantalla y de una parte inferior que cambia según donde pulses en la barra de navegación.

Si pinchas en “sobre nosotros” ves una breve descripción de lo que es BankConsulting (por defecto cuando abres la web).

Si pinchas sobre “Consulta nombres” te aparece un formulario en el que escribes nombre y/o apellidos de el/los cliente/s que quieres filtrar. Al pinchar sobre “Enviar” te aparecen los clientes que cuadran con la búsqueda en la tabla inferior.

Si pinchas sobre “Consulta transacciones” te aparecen 3 columnas.

HTML

Barra de Navegación de bootstrap5:

Contenido de las diferentes secciones de la barra de navegación:

La web tiene un footer con el contacto

En el head del html hemos añadido la hoja de estilos de bootstrap y una creada por mí, style.css

CSS

fuente: https://developer.mozilla.org/en-US/docs/Web/CSS/flex-direction*

JavaScript mouseover

Quiero que cuando pase el ratón por encima de:

  1. un párrafo, la letra se ponga en negrita
  2. una imagen, se haga más grande

Párrafo

Seleccionamos todos los párrafos que tienen la clase “cambiar” (los tienen todos, lo he puesto yo). Lo guardamos en variable parrafos.

con forEach hacemos dos event listener:

Imágenes

Sigue el mismo principio, pero con nuevaClase2 y se seleccionan las imágenes.

JS sugerencias

Hay un input text para escribir título de la sugerencia y textarea para escribir la sugerencia. Al pinchar sobre “Finalizar” se añade lo que se haya escrito después de “Sugerencias realizadas”. El título se mostrará pero la sugerencia estará oculta.

Cuando pincho sobre botón primero veo si no se ha añadido nada como título o sugerencia, en caso afirmativo saco alerta y NO CAMBIO num_sugerencias.

Si tengo titulo Y sugerencia, creo un elemento div que va a tener como hijos al título, con etiqueta h3, y texto de la sugerencia, con etiqueta p (que lo oculto al final).

Posteriormente, dejo en blanco el input y el textarea.

Para mostrarlo añadí una función aparece un observador(?) de eventos “onclick” como se ve en la siguiente foto:

Defino la función aparece que recibe el evento. uso .target para obtener el elemento que “sufre(?)” el evento, en este caso el h3. Tomo su id y lo guardo en la variable texto.

La id del h3 y del texto contienen el mismo número luego puedo acceder al texto que corresponde al h3 sobre el que he pinchado fácilmente.

Si está en style.display tiene el valor “block”, entonces se está mostrando, luego lo oculto dándole el valor “none”. En caso contrario,es decir, tiene el valor “none”, significa que no se está mostrando, luego le doy valor “block” para que sí se muestre.

Decidí hacerlo con *

En esta sección no he añadido lo de cambiar la clase css al pasar el ratón por encima el ratón para no complicar más la cosa.

Posible mejora

Hacer una base de datos en la que guarde las sugerencias para que cuando se cierre el navegador no se pierdan.

Además, añadir la funcionalidad de eliminar sugerencias.

Consultas

Consulta_nombres

Introduces nombre y/o apellidos y en la tabla azul te salen los registros que concuerden.

Tomo el formulario en la variable formulario y espero que suceda el evento “submit” para aplicar una función. Primero, uso preventDefault para evitar que el navegador me lleve al documento php. Luego, tomo los datos del formulario con new FormData (en consola se puede ver las propiedades de dicho objeto).

Hago una consulta mediante el método “POST” a consulta_nombres.php (el fetch).

El php recibe nombre y apellido y lo guarda en variables, establece la conexión mediante new PDO. Si el intento de conexión falla se devuelve “Error: (mensaje de error)”.

Hago la consulta preparada (uso :algo y bindParam en vez de poner directamente $algo en la consulta). fetchAll se usa para pasar de un array con subarrays (cada fila de datos de la consulta es un subarray) a un array asociativo.

ejemplo array predeterminado

ejemplo array asociativo

eb.

Hago json_encode y el echo.

Vuelvo al JS.

  1. Se borra la tabla en la que se visualizan los datos en el HTML, para que podamos visualizar la nueva consulta.
  2. Si la respuesta de la consulta no es vacía se añade una fila en la tabla del HTML por cada documento (fila de la consulta) del JSON.

Si la respuesta es vacía se colapsan las columnas y se hace sólo una fila con el mensaje “No se encontraron resultados”

Consulta_transacciones

De qué trata explicado en Página web.

Procedemos de manera parecida a consulta_nombres co el addEventListener. Tomamos en divisaSeleccionada todas las divisas que han sido seleccionadas. divisaSeleccionada es un nodeList, que aunque es un objeto parecido a un Array no lo es, así que lo convertimos en un array con Array.fromy lo guardamos en divisaSeleccionada2.

La consulta que vamos a hacer es mediante el método get. Sería más apropiado hacerlo mediante el método post, ya que tenemos una array, pero vamos a tomarlo como un reto. Para solucionar el problema iteramos en el array y hacemos varias consultas get invidualmente y añadimos a la tabla para visualizar datos. Borramos la tabla cada vez que le damos al botón del formulario.

El php es una consulta get estándar salvo porque revisa que si no hay divisas seleccionadas hace la consulta teniendo en cuenta sólo la fecha, es decir, es como si hubiera seleccionado todas las divisas.

Insertar, eliminar datos

Voy a insertar clientes. Para ello inserto Nombre, Apellido y Fecha de nacimiento, además pongo ID_Cuenta que tiene, si no añado ningún ID_Cliente no se le asocia cuenta. La cuenta debe existir de antes.

Elimino clientes por el ID, si la cuenta que tenía asociada, se queda sin ningún cliente, elimino también la cuenta.

Problema surgido de array de arrays Tras la siguiente consulta (consulta16):

$resultado queda como una array de arrays, como se puede ver con un vardump:

(este vardump ha sido en un prueba.php y con unos datos que he añadido de prueba)

Lo he solucionado accediendo al elemento (INT) mediante la key del array “ID_Cuenta”.